package jpave.database;

/** 
 * This class provides the database routines for the Core Annotator.
 */

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.*;

import java.util.Vector;
import java.util.Iterator;
import java.util.HashMap;
import java.util.TreeMap;
import java.util.HashSet;
import java.util.TreeSet;
import java.util.Map.Entry;
import java.util.ArrayList;

import util.methods.*;
import util.Debug.ErrorReport;

import jpave.annotator.CoreMain;
import jpave.dataholders.BlastHitData;
import jpave.dataholders.ContigData;
import jpave.dataholders.SequenceData;
import jpave.dataholders.CodingRegion;
import jpave.dataholders.AlignmentData;

public class CoreDBWrapper {
	private boolean debug = CoreMain.debug;
	
	private boolean bFirstAnno = false;
	private boolean bReAnnotate = false;
	private boolean bGOtree = false;
	private String annoVer="v1.0", annoDate="";
	public boolean isFirstAnno() { return bFirstAnno;}
	public boolean isReAnnotate() { return bReAnnotate;}
	public boolean existsGOtree() { return bGOtree;}
	public String getAnnoVer() { return annoVer;}
	public String getAnnoDate() { return annoDate;}
	
	// Used only by ManagerFrame to deleteAnnotation
	public CoreDBWrapper (String paveHost, String paveDB, String paveUser, String pavePW) 
		throws Exception
	{
		PAVE_host = paveHost;
		PAVE_db = paveDB;
		PAVE_user = paveUser;
		PAVE_password = pavePW;
		createDBConnection(); // dies if fails
	}
	public CoreDBWrapper (boolean doAnno, String paveHost, String paveDB, String paveUser, 
			String pavePW, String assmID   ) 
		throws Exception
	{
		PAVE_host = paveHost;
		PAVE_db = paveDB;
		PAVE_user = paveUser;
		PAVE_password = pavePW;
		strAssemblyID = assmID;
	
		createDBConnection(); // dies if fails
		
		if (fltDbVersion == 0) 
			fltDbVersion = Version.fltSchema(dbConnection);
		
	    Statement stmt = null;
	    ResultSet rs = null;
	    try {           
		    stmt = dbConnection.createStatement(
	        		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        String strQ = "SELECT AID, username, projectpath, assemblydate, annotationdate " +
	    			"FROM assembly WHERE assemblyid='" + strAssemblyID + "'";
	  
		    	rs = stmt.executeQuery ( strQ );
		    	if ( !rs.next() ) {
		    		CoreMain.die("No sequences for singleTCW ID=" + strAssemblyID + ". Has Instantiate been executed?");       	
		    	}
		   
		    	AID = rs.getInt("AID");
		    	username = rs.getString("username");
		    	projectpath = rs.getString("projectpath");
		    	assemblydate = rs.getString("assemblydate");
		    	try {
		    		annotationdate = rs.getString("annotationdate");
		    	}
		    	catch(Exception e) {
		    		annotationdate = "";
		    	} 
		   
		    	bFirstAnno = false;
		    	boolean annoExist=false;
		    	String anno = "Annotation:   " + annotationdate;
			if (annotationdate == null || annotationdate.equals("") || annotationdate.equals("2000-11-09")) {
				anno = "This is the first annotation.";
				bFirstAnno = true;
			} 
			else if (annotationdate.equals("1112-11-11")) {
				anno = "Previous annotation was deleted.";
			}// if change here, change in OverviewDBWrapper
			else {
				annoExist=true;
				if (annotationdate.equals("1111-11-11")) 
					anno = "Previous annotation started but did not finish.";
				else if (annotationdate.equals("1111-11-12")) 
					anno = "Previous annotation finished but similarity did not finish.";
				else
					anno = "Previous annotation was completed on " + annotationdate + ".";
			}
			if (tableColumnExists("schemver", "annoVer")) {
				rs = stmt.executeQuery("Select annoVer, annoDate from schemver");
				if (rs.next()) {
					annoVer = rs.getString(1);
					annoDate = rs.getString(2);
				}		
			}
		    	CoreMain.PrtSpMsg(1, "sTCW ID:      " + strAssemblyID);
		    	CoreMain.PrtSpMsg(1, "Create:       " + assemblydate);
		    	CoreMain.PrtSpMsg(1, "User Name:    " + username);
		    	CoreMain.PrtSpMsg(1, "Project Path: " + projectpath);
		    	isProteinDB = tableColumnExists("assem_msg", "peptide");
		    	if (isProteinDB) CoreMain.PrtSpMsg(1,"Protein database");
		    	CoreMain.PrtSpMsg(1,anno);
		    	
		    	if (doAnno && !bFirstAnno && annoExist) {
		    		CoreMain.PrtSpMsg(1,"");
		    		if (CoreMain.yesNo("Annotation exists in database. Delete existing annotation")) {	
		    			CoreMain.bDelAnno = true; // deletes after all checks
		    			bFirstAnno = true;
		    			bReAnnotate = true;
		    		}
		    		else if (!CoreMain.yesNo("Add to existing annotation [y] Exit [n]")) 
		    			CoreMain.die(" User request");
		    		else if (!Version.strPAVEver.equals(annoVer)) {
		    			//CoreMain.PrtWarn("Existing annotation was generated with TCW" + annoVer);
		    			//CoreMain.PrtWarn("Since this is verion " + Version.strPAVEver + " there may be differences");
		    		}
		    	}	
		    	bGOtree = tableExists("pja_gotree");
	    }
		catch (SQLException e)
		{
			ErrorReport.die(e, "Unable to connect to " 	+ dbFullPath);
		}
		catch (Exception e)
		{
			ErrorReport.die(e, "Unable to query " 	+ dbFullPath);
		}
	}
	public void updateAnnoVer() {
		try {
			addColumnKludge(dbConnection);
			Statement stmt = dbConnection.createStatement(
	        		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			stmt.executeUpdate("update schemver set annoVer='" + Version.strPAVEver + "'");
			stmt.executeUpdate("update schemver set annoDate='" + Version.strRelDate + "'");
		}
		catch (Exception e) {
			ErrorReport.reportError(e, "Updating annotation verion");
		}
	}
	// called from overview and above
	static public void addColumnKludge(Connection dbConn) {
		try {
			Statement stmt = dbConn.createStatement(
	        		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			if (!tableColumnExists(dbConn, "schemver", "annoVer")) {
				stmt.executeUpdate("alter table schemver add annoVer tinytext");
				stmt.executeUpdate("alter table schemver add annoDate tinytext");
			}
		}
		catch (Exception e) {
			ErrorReport.reportError(e, "Updating annotation verion");
		}
	}
	static public boolean tableColumnExists(Connection dbConn, String table, String column) throws Exception
	{
		boolean ret=false;
		Statement stmt = dbConn.createStatement();		
		ResultSet rs = stmt.executeQuery("show columns from " + table + " where field='" + column + "'");
		if (rs.first()) ret = true;
		rs.close();
		return ret;
	}
	private void createDBConnection() throws Exception {

		dbFullPath = "jdbc:mysql://" + PAVE_host + "/" 	+ PAVE_db;
		CoreMain.Print("\nConnecting to database " + PAVE_host + "/" 	+ PAVE_db );
		
		String driver = "com.mysql.jdbc.Driver";
		try {
			Class.forName(driver).newInstance();
		} catch (Exception e) {
			String s = "Failed to load the database driver";
			ErrorReport.die(e, s);
			throw new RuntimeException(
					"Failed to load the database driver class with name \""
							+ driver + "\".\n" + e.getMessage());
		}
		reset();
	}
	
	public static boolean checkMysqlDB(String host, String db, String user, String pass) 
	{
		String dbstr = "jdbc:mysql://" + host + "/" + db;
		Connection con = null; 

		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(dbstr, user, pass);
			con.close();
			return true;
		} 
		catch (Exception e)
		{
			ErrorReport.die("*** Fatal Error: Database " + db + " not found on host " + host);	
		}
		return false;
	}	
	
	// Called from annotator before performing a bunch of database ops
	public void reset() throws Exception
	{
		try
		{
			if (dbConnection != null && !dbConnection.isClosed()) 
			{
				dbConnection.close();
			}
			
			Class.forName("com.mysql.jdbc.Driver");
			DriverManager.setLoginTimeout(30 /* seconds */);
			dbConnection = DriverManager.getConnection(dbFullPath, PAVE_user, PAVE_password);
			//dbConnection.setAutoCommit(false);
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
			fatalError("Unable to connect to " 	+ dbFullPath, e);
		}
	}
	
	// all loads need to do this periodically and at the end 
	// using autocommit(true) is slow on large loads
	public void commitChanges ( ) 
	{
		try
		{
			//dbConnection.commit();
		}
		catch(Exception e)
		{
			fatalError("Unable to commit chnages ", e);
		}
	}
	
	public void close ( ) throws Exception
	{
	   dbConnection.close();
	}
	
	public void fatalError (String s, Exception e) {
		try {
			dbConnection.close();
		}
		catch (Exception err) {
			ErrorReport.die(err, "Cannot close database connection");
		}
		if (e != null) ErrorReport.die(e, s);
		CoreMain.die(s);	
	}
	
	public boolean tableExists(String name) 
	{
		try {
			Statement stmt = dbConnection.createStatement();		
			ResultSet rs = stmt.executeQuery("show tables like '" + name + "'");
			return rs.first();
		}
		catch(Exception e)
		{
			ErrorReport.die(e);
		}
		return false;
	}
	
	public boolean tableColumnExists(String table, String column) throws Exception
	{
		boolean ret=false;
		Statement stmt = dbConnection.createStatement();		
		ResultSet rs = stmt.executeQuery("show columns from " + table + " where field='" + column + "'");
		if (rs.first()) ret = true;
		rs.close();
		return ret;
	}

	/**
    * If the PAVE.cfg parameter reStart is set to 'yes' for jPAVE annotation
    * this is called to remove all fields and tables populated by annotation
    */
   public void deleteAnnotation ( ) throws Exception
   {
	   CoreMain.PrtSpMsg(0, "Deleting all previous annotation data");
       try
       {
           Statement stmt = dbConnection.createStatement( ResultSet.TYPE_FORWARD_ONLY, 
           		ResultSet.CONCUR_UPDATABLE); 
 
       	   stmt.executeUpdate("update assem_msg set pja_msg = NULL where AID = 1");
       	   stmt.executeUpdate("UPDATE contig SET PID = NULL, bestmatchid = NULL, PIDov = NULL, " +
           		"cnt_overlap = 0, cnt_species = 0, cnt_gene = 0, " +
           		"cnt_swiss = 0, cnt_trembl = 0, cnt_nt = 0, cnt_pairwise = 0, cnt_taxo = 0, " +
				"o_coding_start = NULL, o_coding_has_begin = 0, " +  
				"o_coding_end = NULL, o_coding_has_end = 0, o_frame = NULL, " +
				"p_coding_start = NULL, p_coding_has_begin = 0, " +  
				"p_coding_end = NULL, p_coding_has_end = 0, p_frame = NULL ");
       	  
       	   if (tableColumnExists("contig", "cnt_gi")) // CAS 2/8/15 db ver 4.1
       		   stmt.executeUpdate("update contig set cnt_gi=0");
       	   else 
       		   stmt.executeUpdate("alter table contig add cnt_gi int unsigned default 0");
       		   
       	   stmt.executeUpdate("UPDATE assembly SET annotationdate='1112-11-11' WHERE AID=1");
       	   
           deleteTable(stmt, "pja_pairwise");
           deleteTable(stmt, "pja_db_unitrans_hits");
           deleteTable(stmt, "pja_db_unique_hits");
           deleteTable(stmt, "pja_databases");
           deleteTable(stmt, "pja_db_species");
           stmt.close();
       }
       catch(Exception e) {ErrorReport.die(e,"Error deleting annotation data - try again...");}  	   	
   }
   public void deleteSpecies() {
       try
       {
           Statement stmt = dbConnection.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); 
 
       	   stmt.executeUpdate("update assem_msg set pja_msg = NULL where AID = 1");
           deleteTable(stmt, "pja_db_species");
           stmt.close();
       }
       catch(Exception e) {
	    	   ErrorReport.die(e,"Error deleting data - try again...");
       }  	   	
   }
 
   private void deleteTable(Statement stmt, String table) {
	   
	   	try {
	       String strQ = "DELETE FROM " + table;
	       stmt.executeUpdate ( strQ );
	       
	       ResultSet rset = stmt.executeQuery("SELECT COUNT(*) FROM " + table);
	       rset.next();
	       int cnt = rset.getInt(1);
	       rset.close();
	       
	       strQ = "ALTER TABLE " + table + " AUTO_INCREMENT = " + cnt+1;
	       stmt.executeUpdate (strQ);
	       
	    }
	    catch(Exception e) {
	    	String err = "Fatal error deleting table " + table;
		    ErrorReport.die(e,err);
		    System.exit(-1);
	     }
   }
   /** called from CoreAnnotater: do homologyTest
    *	and UniProtBlastProcessor for annotation
    */
   public ContigData loadContigDataForORF ( int ctgID, String ctgName ) 
   {
       Statement stmt = null;
       ResultSet rset = null;
       
       try
       {
           ContigData curContig = new ContigData ();
           
           stmt = dbConnection.createStatement(
        		   ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
 
           String strQuery = "SELECT consensus, consensus_bases, numclones, bestmatchid, PID, PIDov " +
       			"FROM contig " +
       			"WHERE contig.CTGID = "  + ctgID; 
           
           rset = stmt.executeQuery( strQuery );
           if ( !rset.next() ) {
           	   fatalError("No sTCW database entry for " + ctgName , null);
           }

           curContig.setContigID( ctgName);
           curContig.setCTGID( ctgID );
           
           String seqString = ( getStringFromReader (
           		rset.getCharacterStream( 1 ) ) ).trim();       
           SequenceData consensus = new SequenceData ("consensus");
           consensus.setIsDNA(); // CAS 3/11/13
           consensus.setName( ctgName );
           consensus.setSequence ( SequenceData.normalizeBases( seqString, '*', '.' ) );
           curContig.setSeqData( consensus );
           curContig.setConsensusBases(consensus.getLength());
           curContig.setNumSequences( Integer.parseInt(rset.getString(3)) ); 
           curContig.setBestMatch(rset.getString(4));
           
           // ADD hitData for best eval hit so can do protein orf
           int PID = rset.getInt(5);
           if (PID != 0) {
        	   		strQuery = "SELECT ctg_start, ctg_end, isProtein from pja_db_unitrans_hits where PID=" + PID;
        	   		rset = stmt.executeQuery( strQuery );
                if ( !rset.next() ) 
                 	  fatalError("Internal error: No PID " + PID , null);
       
                BlastHitData hit = new BlastHitData(rset.getInt(1), rset.getInt(2), rset.getInt(3), PID);
                curContig.setBestEval(hit);
           }
           if ( rset != null ) rset.close();
           if ( stmt != null ) stmt.close(); 
           return curContig;
       }
       catch (Exception e) {
	    	   ErrorReport.die(e, "Loading Consensus and Data");
	    	   return null;
       }
   }
  
   // for annotator pairwise
   public ContigData loadContigData (String ctgName ) 
   {
       Statement stmt = null;
       ResultSet rset = null;
       
       try
       {
           ContigData curContig = new ContigData ();
           
           stmt = dbConnection.createStatement(
        		   ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
 
           String strQuery = "SELECT consensus, consensus_bases, numclones, bestmatchid, CTGID " +
       			"FROM contig " +
       			"WHERE contig.contigid = '"  + ctgName + "'"; 
           
           rset = stmt.executeQuery( strQuery );
           if ( !rset.next() ) {
           	   fatalError("No sTCW database entry for " + ctgName , null);
           }

           curContig.setContigID( ctgName);
           curContig.setCTGID( rset.getInt(5) );
           
           String seqString = ( getStringFromReader (
           		rset.getCharacterStream( 1 ) ) ).trim();       
           SequenceData consensus = new SequenceData ("consensus");
           
           consensus.setName( ctgName );
           consensus.setSequence ( SequenceData.normalizeBases( seqString, '*', '.' ) );
           curContig.setSeqData( consensus );
           curContig.setConsensusBases(consensus.getLength());
           curContig.setNumSequences( Integer.parseInt(rset.getString(3)) ); 
           curContig.setBestMatch(rset.getString(4));
           
           if ( rset != null ) rset.close();
           if ( stmt != null ) stmt.close(); 
           return curContig;
       }
       catch (Exception e) {
	    	   ErrorReport.die(e, "Loading Consensus and Data");
	    	   return null;
       }
   }
   // called from loadConsensus above
	private static CodingRegion loadCodingRegion ( ResultSet rset, 
	    		int nBases, int nType, String strBegin, String strHasBegin,
				String strEnd, String strHasEnd,
				String strFrame ) throws Exception
	{
	        if ( rset.getString( strFrame ) != null )
	        {	        	
		        	CodingRegion coding = new CodingRegion ( nType );
		        	coding.setFrame( rset.getInt( strFrame ) );
		        	coding.setBegin( rset.getInt( strBegin ) );
		        	coding.setEnd( rset.getInt( strEnd ) );
		        	coding.setHasBegin( rset.getBoolean(strHasBegin) );
		        	coding.setHasEnd( rset.getBoolean(strHasEnd) );
		        	return coding;
	        }
	        else 
	        		return null;
	}
	/*
	 * check at beginning of annotation whether databases have already been used
	 */
    public ArrayList <String> loadDatabases (  ) throws Exception
    {
       Statement stmt = null;
       ResultSet rset = null;

       try {
           ArrayList <String> paths = new ArrayList <String> ();
           stmt = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
           stmt.setFetchSize(Integer.MIN_VALUE); 

           String strQuery = "SELECT path FROM pja_databases " +
           		"WHERE AID = " + AID;
 
           rset = stmt.executeQuery( strQuery );
           
           while(rset.next())
               paths.add ( rset.getString(1) );
               
           return paths;
       }
		catch (SQLException e) {
			String err = "cannot load Blast databases from PAVE database";
			ErrorReport.die(e, err);
		} 
       finally {
           if ( stmt != null ) stmt.close();
           if ( rset != null ) rset.close();
       }
       return null;
   }    
	/************************************************************
	 * Load routines used by annotating with DB hits
	 */ 
	/**********************************************************************************
	 * Load all DB ids for a given contig in order to compute their filter
	 * plus sequence because need to compute protein ORF using bestEval
	 **********************************************************************************/
	public ArrayList <BlastHitData>loadHitDataForCtg (ContigData ctgData) throws Exception
	{
        Statement stmt = null;
        ResultSet rset = null;

        ArrayList <BlastHitData> hitList= new ArrayList <BlastHitData> ();
        try
        {
            stmt = dbConnection.createStatement(
            		ResultSet.TYPE_FORWARD_ONLY,
            		ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE); // supposed to prevent memory hogging on large query
   
            String ctgName = ctgData.getContigID();
            int ctgID = ctgData.getCTGID();
            String strQ = 	"SELECT " +
            		"pja_db_unique_hits.DBID, " +
		    		"pja_db_unique_hits.DUHID, pja_db_unique_hits.hitID, " +
		    		"pja_db_unique_hits.description, pja_db_unique_hits.sequence, " + 
		    		"pja_db_unique_hits.species, pja_db_unique_hits.dbtype, pja_db_unique_hits.taxonomy, " +
		    		
		    		"pja_db_unitrans_hits.PID, " +
		    		"pja_db_unitrans_hits.uniprot_id, pja_db_unitrans_hits.percent_id, pja_db_unitrans_hits.alignment_len," +
		    		"pja_db_unitrans_hits.ctg_start, pja_db_unitrans_hits.ctg_end, " +
		    		"pja_db_unitrans_hits.prot_start, pja_db_unitrans_hits.prot_end, " +
		    		"pja_db_unitrans_hits.e_value, pja_db_unitrans_hits.bit_score," +
		    		"pja_db_unitrans_hits.cnt_hsps, " +
		    		
		    		"pja_db_unitrans_hits.blast_rank, pja_db_unitrans_hits.isProtein, " +
		    		"pja_db_unitrans_hits.filtered " +
		    		
		    		"FROM pja_db_unique_hits " +
		    		"JOIN pja_db_unitrans_hits " +
		    		"ON pja_db_unitrans_hits.DUHID = pja_db_unique_hits.DUHID " +
		    		"WHERE   pja_db_unitrans_hits.CTGID = " + ctgID + " " +
		    		"order by pja_db_unitrans_hits.PID";
		 
            rset = stmt.executeQuery( strQ );
            while( rset.next() )
            {	
		        String line = ctgID + "\t";             
		        line += rset.getString( "uniprot_id" ) + "\t";
		        line += rset.getFloat( "percent_id" ) + "\t";
		        line += rset.getInt("alignment_len") + "\t";
		        line += rset.getInt("ctg_start") + "\t";
		        line += rset.getInt("ctg_end") + "\t";
		        line += rset.getInt("prot_start") + "\t";
		        line += rset.getInt("prot_end") + "\t";
		        line += rset.getDouble( "e_value" ) + "\t"; 
		        line += rset.getDouble( "bit_score" ) + "\t"; 
		        line += rset.getInt("cnt_hsps") + "\t";		        
		        line += rset.getInt( "isProtein" ) + "\t";   
		        line += rset.getInt("blast_rank") + "\t";
		        line += rset.getInt("filtered") + "\t";
		        line += rset.getString( "dbtype" ) + "\t";
		        line += rset.getString( "taxonomy" ) + "\t";
		        line += rset.getString("species") + "\t";
		        line += rset.getString("description") + "\t";
                BlastHitData hitData = new BlastHitData(BlastHitData.DB_UNITRANS, line);
                hitData.setPID(rset.getInt("PID"));
                hitData.setDBID(rset.getInt("DBID"));
                hitData.setCTGID(ctgData.getCTGID());
	    			hitList.add(hitData);
	    		}
	    		return hitList;
        }
        catch(Exception e) {
	        	ErrorReport.die(e, "Error: reading database loadDBhitDataForCtg");
	        	throw e;
        }
        finally 
        {
            if ( rset != null ) rset.close();
            if ( stmt != null ) stmt.close();
        }		
	}

	/************************************************************
	 * Load routines used by doHomology
	 */
   public TreeSet <String> loadContigIDsInAssembly (  ) throws Exception
   {
       Statement stmt = null;
       ResultSet rset = null;

       try {
           TreeSet<String> theIDs = new TreeSet<String> ();
           stmt = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
        		   ResultSet.CONCUR_READ_ONLY);
           stmt.setFetchSize(Integer.MIN_VALUE); 

           String strQuery = "SELECT contigid FROM contig ";
           rset = stmt.executeQuery( strQuery );
           
           while(rset.next()) 
               theIDs.add( rset.getString("contigid") );
               
           stmt.close();
           rset.close();
           return theIDs;
       }
       catch (SQLException e) {
			fatalError("cannot load Contigs IDs from database", e);
       } 
       return null;
   } 
   
   public TreeMap <String, Integer> loadContigMap (  )
   {
       Statement stmt = null;
       ResultSet rset = null;

       try {
           TreeMap<String, Integer> theIDs = new TreeMap<String, Integer> ();
           stmt = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
        		   ResultSet.CONCUR_READ_ONLY);
           stmt.setFetchSize(Integer.MIN_VALUE); 

           String strQuery = "SELECT contigid, CTGID FROM contig ";
           rset = stmt.executeQuery( strQuery );
           
           while(rset.next()) 
               theIDs.put( rset.getString(1), rset.getInt(2) );
               
           stmt.close();
           rset.close();
           return theIDs;
       }
       catch (SQLException e) {
			fatalError("cannot load Contigs IDs from database", e);
       } 
       return null;
   }    
    	
   /*
    * add pairs to pairHash that shared hits for homology
    * a contig pair can share mulitple DB hits. 
    * Only the best is kept and the cnt_shared_hits is computed
    * XXX this runs out of memory on large projects, so currently not used
    */    
   public void loadSharedHitsCnt (BlastHitData hitData ) throws Exception
   {
       Statement stmt = null;
       ResultSet rset = null;
       String strQ;
  	
       try {
           stmt = dbConnection.createStatement(
           	ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
           
           	String ctgID1 = hitData.getContigID();
           	String ctgID2 = hitData.getHitID();
 
	        strQ = "SELECT COUNT(*) " +
	        		" FROM pja_db_unitrans_hits as p1 " +
	        		" JOIN pja_db_unitrans_hits as p2 " +
	        		" WHERE p1.contigid = '" + ctgID1 + "'" +
	        		" AND   p2.contigid = '" + ctgID2 + "'" +
	        		" AND p1.uniprot_id = p2.uniprot_id";

	        rset = stmt.executeQuery( strQ );
	        rset.next ();
	    		int nBest = rset.getInt(1);
	    		hitData.setCntSharedHits(nBest);
        }
		catch (SQLException e) {
			String err = "cannot load DB hit contig pairs from database";
			ErrorReport.die(e, err);
			throw e;
		} 
       finally {
       		if ( stmt != null ) stmt.close();
       		if ( rset != null ) rset.close();
       }
   }
   
    /*
     * add pairs to pairHash that shared hits for homology
     * a contig pair can share mulitple DB hits. 
     * Only the best is kept and the cnt_shared_hits is computed
     * XXX this runs out of memory on large projects, so currently not used
     */    
    public int loadPairsSharingDBHits (
    		HashMap <String, BlastHitData> pairsHash ) throws Exception
    {
        Statement stmt = null;
        ResultSet rset = null;
        String key, strQuery, lastHitName="";
        ArrayList<CtgDBHitInfo> listForLast = new ArrayList<CtgDBHitInfo> ();
        int good=0;
   	
        try {
            stmt = dbConnection.createStatement(
            	ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            
	        strQuery = "SELECT uniprot_id, contigid,  percent_id,  " +
	        		" ctg_start, ctg_end, " +
	        		" prot_start, prot_end, isProtein  " +
	            	" FROM pja_db_unitrans_hits " + 
	            	" WHERE AID = " + AID + 
	            	" ORDER BY uniprot_id";
  
            rset = stmt.executeQuery( strQuery );
 
            /** find all contigs that align to the same protein **/
            while(rset.next()) 
            {
            		String curHit = rset.getString("uniprot_id");
         
            		if ( !curHit.equals( lastHitName ) ) // all unitrans with hit have been found
            		{
            			good += loadPairsSameHit(lastHitName, pairsHash, listForLast);      			
            			listForLast.clear();
            			lastHitName = curHit;            		
            		} // end  if for same DB hit
            	
            		// Collect all of the contigs that hit this protein
            		CtgDBHitInfo hit = new CtgDBHitInfo();
            		hit.ctgName =  rset.getString("contigid") ;
            		hit.sim = rset.getDouble("percent_id");
            		hit.hitStart = rset.getInt("hit_merge_start");
            		hit.hitEnd = rset.getInt("hit_merge_end");
            		if (hit.hitStart > hit.hitEnd) hit.compH = true;
            		else hit.compH = false;
               	hit.ctgStart = rset.getInt("ctg_merge_start");
               	hit.ctgEnd = rset.getInt("ctg_merge_end");
           		if (hit.ctgStart > hit.ctgEnd) hit.compC = true;
           		else hit.compC = false;
           		hit.isProtein = rset.getInt("isProtein");
           		
               	listForLast.add(hit);
            } // end while loop through database entries
            return good; 
        }
		catch (SQLException e) {
			String err = "cannot load DB hit contig pairs from database";
			ErrorReport.die(e, err);
			throw e;
		} 
        finally {
        		if ( stmt != null ) stmt.close();
        		if ( rset != null ) rset.close();
        }
    }
    
    private int loadPairsSameHit(String hitName,
    			HashMap <String, BlastHitData> pairsHash,
    			ArrayList<CtgDBHitInfo> listForLast)
    {
        BlastHitData oldHit;
        String key;
        int good = 0;
        
    		for ( int i = 0; i < listForLast.size(); ++i ) 
		{
			CtgDBHitInfo ctg1 = listForLast.get(i);      

			for ( int j = i + 1; j < listForLast.size(); ++j ) 
			{
				CtgDBHitInfo ctg2 = listForLast.get(j);

				if ( ctg1.ctgName.equals( ctg2.ctgName )) continue;
				
				int s1, e1, s2, e2;
				int olap=0;
				
				// it appears NT can be reverse but AA will not (or maybe the target never is)
				if (ctg1.compH != ctg2.compH) continue;
				
				if (ctg1.compH) {
					s1=ctg1.hitEnd; e1=ctg1.hitStart;
					s2=ctg2.hitEnd; e2=ctg2.hitStart;
				}
				else {
					s1=ctg1.hitStart; e1=ctg1.hitEnd;
					s2=ctg2.hitStart; e2=ctg2.hitEnd;
				}
			
				// their overlap is based on where the unitrans align to the DB hit	
				if (s1 <= s2 && e1 > s2 && e1 <= e2 )  //overlap
					olap = e1-s2; 
				else if (s2 <= s1 && e2 > s1 && e2 <= e1)  // overlap
					olap = e2-s1; 
				else if (s1 <= s2 && e1 >= e2)  // contained
					olap = e2-s2; 
				else if (s2 < s1 && e2 > e1)  // contained
					olap = e1-s1; 
				else continue;
				
				if (olap <= 20) continue; // XXX tiny heuristic

				double sim = Math.min(ctg1.sim,ctg2.sim);
				
				if (ctg1.ctgName.compareTo(ctg2.ctgName) > 0) 
				 	 key = ctg1.ctgName + ";" + ctg2.ctgName;  
				else key = ctg2.ctgName + ";" + ctg1.ctgName;
			
				// heuristic - replace existing if better sim and olap
				// the s1 and s2 are original coordinates, needed for RF
				// e1 and e2 are not used... olap is alignment_len
				if (pairsHash.containsKey(key)) {
					oldHit = pairsHash.get(key);
					oldHit.pairsBestDBhit(sim, olap, s1, e1, s2, e2); 
					continue;
				}
			
				String line = ctg1.ctgName + "\t" + ctg2.ctgName + "\t";                                
				line += sim + "\t" + olap + "\t";  
				line += s1 + "\t" + e1 + "\t" + s2 + "\t" + e2 + "\t";
				line += hitName;
				BlastHitData hit = new BlastHitData(BlastHitData.DB_CTGPAIRS, line);      							
				pairsHash.put(key, hit);
				good++;
			} // inner loop through all pairs with this DB hit
		}  // outer loop through all pairs with this DB hit 
    		return good;
    }
    private class CtgDBHitInfo 
    {
   		String ctgName;
    		int ctgStart, ctgEnd,  hitStart, hitEnd, isProtein;
    		double  sim;
    		boolean compC, compH;
    }
    /* 
     * all pairs have been added to pairsHash - remove those already in database
     */
     public int removePairsInDB (HashMap <String, BlastHitData> pairsHash) throws Exception
     {
         Statement stmt = null;
         ResultSet rset = null;
         int count = 0;
         String key, contig1, contig2;
     	
         try {
             stmt = dbConnection.createStatement( 
             	ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
 	        String strQuery = "SELECT contig1, contig2 FROM pja_pairwise " +
 						      " WHERE AID = " + AID; 
             rset = stmt.executeQuery( strQuery );
             while(rset.next()) {
 				contig1 = rset.getString("contig1");
 				contig2 = rset.getString("contig2");
 				
 				key = contig1 + ";" + contig2;
       			if (pairsHash.containsKey(key)) {
       				pairsHash.remove(key);
       				count++;
       			}
       			key = contig2 + ";" + contig1;
       			if (pairsHash.containsKey(key)) {
       				pairsHash.remove(key);
       				count++;
       			}
             }
         }
 		catch (SQLException e) {
 			String err = "cannot load pairs from database";
 			ErrorReport.die(e, err);
 			count = -1;
 		} 
         finally {
             if ( stmt != null ) stmt.close();
             if ( rset != null ) rset.close();
         }         
         return count;
     }
 
    /*******************  Contig Table ***********************************
     * Called from CoreAnnotator
     **************************************************************/
     public void saveContigORFData ( ContigData theContig ) 
     { 
         String strQ = null;
         Statement stmt = null;
         if (isProteinDB) return;
         
         try {
 	        stmt = dbConnection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, 
 	        		ResultSet.CONCUR_UPDATABLE );
 	              
	        strQ = "UPDATE contig SET " +
	             "  gc_ratio = " + String.valueOf ( theContig.getGCRatio() );
       
	        // ORF coding region
	        strQ += createCodingDBStr ( theContig.getORFCoding(), 
							"o_coding_start", "o_coding_has_begin", 
							"o_coding_end", "o_coding_has_end", "o_frame" ); 
 		        
	     // protein coding region
	        strQ += createCodingDBStr ( theContig.getProteinCoding(), 
							"p_coding_start", "p_coding_has_begin", 
							"p_coding_end", "p_coding_has_end", "p_frame" ); 
 	        
 	        	strQ += " WHERE CTGID = " + theContig.getCTGID();
 	
 	        stmt.executeUpdate( strQ );
 	        if (stmt != null) stmt.close();
         }
 		catch (SQLException e) {
 			ErrorReport.die(e, "cannot save new annotations to database");
         }
     }

    // called by saveContigData and saveDBHitFilter
    private static String createCodingDBStr ( CodingRegion region, 
    		String strBegin, String strHasBegin, String strEnd,
    		String strHasEnd, String strFrame )
    {
    		String s;
        if ( region != null )
        {
           	int b = region.getBegin();
	        	int e = region.getEnd();
	        	if (b < 0) b=0; // field is unsigned 
	        	if (e < 0) e=0;
	        		s = ", " + strBegin + " = " + b +
	        			", " + strEnd + " = " + e +
	        			", " + strFrame + " = " + region.getFrame() +
	                ", " + strHasBegin + " = " + createBooleanString ( region.getHasBegin() ) +
	                ", " + strHasEnd + " = " + createBooleanString ( region.getHasEnd() );
        }
        else
        {	
        		s = ", " + strBegin + " = NULL, " + strHasBegin + " = NULL," +
		    		strEnd + " = NULL, " + strHasEnd + " = NULL," + strFrame + " = NULL";    	
        }
		return s;
    }
  
    public void saveAllCtgPairwiseCnts(HashMap <String, Integer> map) 
    {
        String strQ = null;
        Statement stmt = null;
 
        try {
	        stmt = dbConnection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, 
	        		ResultSet.CONCUR_UPDATABLE );
	        
		    for ( String ctgName : map.keySet() )
	        {
				int cnt = map.get(ctgName);
				strQ = "UPDATE contig SET cnt_pairwise = "   + String.valueOf(cnt) +
	        		" WHERE contigid = '" + ctgName + "' " +
	        		" AND AID = " + AID;
	       
				stmt.executeUpdate( strQ );
			}
      		if (stmt != null) stmt.close();
        }
		catch (SQLException e) {
			fatalError("cannot save contig pairwise counts to database", e);
        }
    }
   
    /******************* Pairwise table **********************
     * CoreAnnotator: doHomologyTest
     * these fields are read by viewPAVE in QueryData, where they are mapped to columns
     * *********************************************************/
    public void savePairwiseAlignments ( String strSaveAssemblyID, 
    		Vector<AlignmentData> alignList ) throws Exception
    {
        if ( alignList.isEmpty() )return;
        
        Statement stmnt = null;
        String strQuery = null;
        AlignmentData ntData = null;
        try
        {
            stmnt = dbConnection.createStatement( 
            		ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
            
            strQuery = "INSERT pja_pairwise " +
                "( AID, assembly_id, contig1, contig2, coding_frame1, coding_frame2, " +
                "NT_hsr_ratio, NT_hsr_score, NT_hsr_len, NT_olp_ratio, NT_olp_score, NT_olp_len, NT_largest_gap, " +
                "AA_hsr_ratio, AA_hsr_score, AA_hsr_len, AA_olp_ratio, AA_olp_score, AA_olp_len, AA_largest_gap, " +
                "in_self_blast_set, in_uniprot_set, in_translated_self_blast," +
                "shared_hitID, cnt_shared_hits, e_value, percent_id, alignment_len, cnt_hsps," +
                "ctg1_start, ctg1_end, ctg2_start, ctg2_end " +
                ") VALUES ";
            String strValues = "";
           
            Iterator<AlignmentData> iterEntry = alignList.iterator ();
            while ( iterEntry.hasNext() )
            {
                ntData = 
                		(AlignmentData)iterEntry.next();
                if (debug) System.err.println("DB: SavePairWise " + ntData.getName1() 
                		+ " " + ntData.getName2());

                if (strValues.length()!=0) strValues += ",";
                strValues += "(" + AID + ",";								
                strValues += "\"" + strSaveAssemblyID + "\", "; 							// assembly_id
                strValues += "\"" + ntData.getName1() + "\", ";					// contig1
                strValues += "\"" + ntData.getName2()  + "\", ";					// contig2
                
                AlignmentData aaData = ntData.getAApwData();
                strValues += String.valueOf( aaData.getFrame1() ) + ", ";		
                strValues += String.valueOf( aaData.getFrame2() ) + ", ";	
                               
                strValues += ntData.getHSRratio() + ", ";  
                strValues += ntData.getHSRmatch() + ", ";							
                strValues += ntData.getHSRlen() + ", ";	
                strValues += ntData.getOLPratio() + ", ";
                strValues += ntData.getOLPmatch() + ", ";					
                strValues += ntData.getOLPlen() + ", ";		
                strValues += "0, "; // largest gap					
                
                strValues += aaData.getHSRratio() + ", ";  
                strValues += aaData.getHSRmatch() + ", ";							
                strValues += aaData.getHSRlen() + ", ";	
                strValues += aaData.getOLPratio() + ", ";
                strValues += aaData.getOLPmatch() + ", ";					
                strValues += aaData.getOLPlen() + ", ";		
                strValues += "0, "; // largest gap	
                            
                BlastHitData hitData = ntData.getHitData();
                strValues += createBooleanString ( hitData.getIsSelf() ) + ", ";	
                strValues += createBooleanString ( hitData.getIsShared () ) + ", ";		
                strValues += createBooleanString ( hitData.getIsTself () )+ ", ";	
               
                strValues += "\"" + hitData.getSharedHitID() + "\", ";
                strValues += hitData.getCntSharedHits() + ",";
                strValues += String.valueOf ( hitData.getEVal() ) + ","; 
                strValues += hitData.getPercentID() + ",";
                strValues += hitData.getAlignLen() + ",";
                strValues += hitData.getCntHSPs() + ",";
                strValues += hitData.getCtgStart() + "," + hitData.getCtgEnd() + ",";
                strValues += hitData.getHitStart() + "," + hitData.getHitEnd();
                strValues += " ) ";
            }
            strQuery += strValues;
            stmnt.executeUpdate ( strQuery );
        }
        catch ( SQLException e )
        {
        	String err = "Save Pairwise Data";
        	if (ntData != null) err += " " + ntData.getName1() + " " + ntData.getName2();
			ErrorReport.die(e,err);
			throw e;
        }
        finally 
        {
            if ( stmnt != null ) stmnt.close();
        }   	
    }
 
    // WN 4/5/11 
    public void dropViewTable() throws Exception {
    	TreeSet<String> tables = new TreeSet<String>();
       	tables.add("pv_" + strAssemblyID);    	
       	tables.add("pv_go_" + strAssemblyID);    	
       	tables.add("pja_unigo");    	
       	tables.add("pja_go_term");   
       	tables.add("pja_db_pir");
       	tables.add("pja_go2go");
       	tables.add("pja_gotree");
       	tables.add("pja_overview");
    	Statement stmt = dbConnection.createStatement(); 
    	for (String tblName : tables)
    	if (tableExists(tblName))
    	{
    		stmt.executeUpdate("drop table " + tblName);
    	}   
    	
    }
    
    public void dropGOTables() throws Exception {
    	Statement stmt = dbConnection.createStatement(); 
    	if (tableExists("go_info"))
    	{
    		stmt.executeUpdate("drop table go_info");
    	}   
    	if (tableExists("pja_gotree"))
    	{
    		stmt.executeUpdate("drop table pja_gotree");
    	} 
    	if (tableExists("pja_unitrans_go"))
    	{
    		stmt.executeUpdate("drop table pja_unitrans_go");
    	}   
    	if (tableExists("pja_uniprot_go"))
    	{
    		stmt.executeUpdate("drop table pja_uniprot_go");
    	}   
    }    
    // error caught by calling routine
	public boolean setAnnotationDate(String date) throws SQLException
	{
		if (date == null) date = "(CAST(NOW() as DATE))";
		
		// CAS 2/8/15 never did work because not specifying AID=1
		String strStmt = "UPDATE assembly SET annotationdate=" + date + " WHERE AID=1";	
        Statement stmt = dbConnection.createStatement( 
        		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );
		stmt.executeUpdate ( strStmt );
        stmt.close();
		
		return true;
	}
	
	public int writeUniTransFile(int uniBlastType, boolean subset, File f) 
	{
		try {
			BufferedWriter file = new BufferedWriter(new FileWriter(f));
			
			Statement stmt = dbConnection.createStatement( 
	        		ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
			
			String strQ = "select contigid,consensus,numclones from contig";
			// the contig hit fields do not get updated until ALL blast hits are loaded
			if (subset) {// kludge, e_frame is not being used
				stmt.executeUpdate("UPDATE contig,pja_db_unitrans_hits " +
						"SET e_frame = 1 WHERE pja_db_unitrans_hits.CTGID=contig.CTGID");
				strQ += " WHERE e_frame is null";
			}
			
	        ResultSet rs = stmt.executeQuery( strQ );

	        int cnt=0;
			while (rs.next())
			{
				String ctgid = rs.getString("contigid");
				String CCS = rs.getString("consensus");
				int numClones = rs.getInt("numclones");
				
				if ( uniBlastType==0 ||
					(uniBlastType==1 && numClones > 1 ) ||
					(uniBlastType==2 && numClones ==1 )) 
				{
					CCS = CCS.toUpperCase();
					file.append(">" + ctgid + " " + numClones + "\n" + CCS.replace("*","") + "\n");
					cnt++;
				}				
			}
			file.close();
			stmt.close();
			return cnt;
		}
		catch (Exception e)
        {
			String s = "Error writing to " + f.getAbsoluteFile();
            ErrorReport.die(e,s);
            return -1;
        }
	}

	// ---------------- To compute RF for AA file -----------------//
	public int aaloadCtgORF(HashMap<String, String> ctgMap) {
	    Statement stmt = null;
	    ResultSet rset = null;

        try {  
           stmt = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
        		   ResultSet.CONCUR_READ_ONLY);
           stmt.setFetchSize(Integer.MIN_VALUE); 

           String strQuery = "SELECT contigid, o_frame, o_coding_start, o_coding_end," +
           		" o_coding_has_begin, o_coding_has_end, cnt_pairwise," +
           		" cnt_swiss, cnt_trembl, cnt_nt, cnt_gi  FROM contig order by contigid";
           rset = stmt.executeQuery( strQuery );
           
           while(rset.next()) {
        	   		int len=0, has=0, nhit=0;
 				String f = rset.getString("o_frame");
 				if (f!=null && !f.equals("0")) {
        	   			len = Math.abs(rset.getInt("o_coding_end") - rset.getInt("o_coding_start")) + 1;
        	   			has = rset.getInt("o_coding_has_begin") + rset.getInt("o_coding_has_end");
           		}
 				else f = "0";
        	   		nhit = rset.getInt("cnt_swiss") + rset.getInt("cnt_trembl") 
        	   					+ rset.getInt("cnt_nt") + rset.getInt("cnt_gi");
 
				String x = 
					f + ";" +
					len + ";" +
					has + ";" +
					nhit + ";" +
					rset.getString("cnt_pairwise");
                ctgMap.put(rset.getString("contigid"), x);
           }  
           stmt.close();
           rset.close();
       }
       catch (SQLException e) {
			fatalError("Cannot load Contigs ORFs from database", e);
       } 
       return ctgMap.size();
	}
	
	public int aaloadCtgHits(String ctgID, Vector <String> ctgVec) 
	{
	    Statement stmt = null;
	    ResultSet rset = null;

        try {  
           stmt = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
        		   ResultSet.CONCUR_READ_ONLY);
           stmt.setFetchSize(Integer.MIN_VALUE); 

           String strQuery = "SELECT contigid, ctg_start, ctg_end, e_value  " +
           		" FROM pja_db_unitrans_hits where contigid = '" + ctgID + "'" +
           		" order by e_value ASC";
           rset = stmt.executeQuery( strQuery );
           
           while(rset.next()) {
				String x =  
						rset.getString("ctg_start")+ ";" + 
						rset.getString("ctg_end") + ";" + 
						rset.getString("e_value");
                ctgVec.add(x);
           }  
           stmt.close();
           rset.close();
        }
        catch (SQLException e) {
			fatalError("Cannot load Sequence Hits from database", e);
        } 	
		return ctgVec.size();
	}
	
	public int aaloadCtgPairs(String ctgID, Vector <String> ctgVec) 
	{	
	    Statement stmt = null;
	    ResultSet rset = null;

        try {  
           stmt = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
        		   ResultSet.CONCUR_READ_ONLY);
           stmt.setFetchSize(Integer.MIN_VALUE); 

           String strQuery = "SELECT contig1, contig2, coding_frame1, coding_frame2, " +
           		" e_value, ctg1_start, ctg1_end  " +
           		" FROM pja_pairwise " +
           		" where contig1 = '" + ctgID + "' or contig2 = '" + ctgID + "'" +
           		" order by e_value ASC";
           rset = stmt.executeQuery( strQuery );
           
           while(rset.next()) {
        	   		int frame=0;
        	   		if (ctgID.equals(rset.getString("contig1")))
        	   			frame = rset.getInt("coding_frame1");
        	   		else if (ctgID.equals(rset.getString("contig2")))
    	   				frame = rset.getInt("coding_frame2");
        	   		else CoreMain.die("error geting contig pairs");
        	   		int len = rset.getInt("ctg1_end") - rset.getInt("ctg1_start") + 1;
        	   		
				String x = frame + ";" + len + ";" +
							rset.getString("e_value") + "; ";
                ctgVec.add(x);
           }  
           stmt.close();
           rset.close();
        }
        catch (SQLException e) {
			fatalError("Cannot load Contig Pairs from database", e);
        } 	
		return ctgVec.size();
	}

	/*************************************************************
	 * This is a special case -- if pairs have been created but a rep file with different cutoffs is wanted
	 */
	public String [] getPairs() {
		try {
			Statement stmt = dbConnection.createStatement(
	        		ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ResultSet rs;
			
			HashMap <String, Integer> clen = new HashMap <String, Integer> ();
			rs = stmt.executeQuery("SELECT contigid, consensus_bases from contig");
			while (rs.next()) {
				clen.put(rs.getString(1), rs.getInt(2));
			}
			rs.close();
			CoreMain.PrtSpMsg(2, "Read " + clen.size() + " contig names ");
			
			rs = stmt.executeQuery("SELECT COUNT(*) FROM pja_pairwise");
		    rs.next();
		    int cnt = rs.getInt(1);
		    rs.close();
		     
		    String [] pairs = new String [cnt];
		    int i=0;
		    
		    rs = stmt.executeQuery("Select contig1, contig2, NT_olp_ratio, NT_olp_score, NT_olp_len " +
		    		"from pja_pairwise");
		    while (rs.next()) {
		    		String c1 = rs.getString(1);
		    		String c2 = rs.getString(2);
		    		if (!clen.containsKey(c1)) CoreMain.die("no " + c1);
		    		if (!clen.containsKey(c2)) CoreMain.die("no " + c2);
		    		if (clen.get(c1) < clen.get(c2)) {
		    			String t = c1;
		    			c1 = c2;
		    			c2 = t;
		    		}
		    		String sim = rs.getString(3);
		    		double d = (double) rs.getInt(4) / rs.getInt(5);
		    		pairs[i] = c1 + ";" + c2 + ";" + sim + ";" + d;
		    		i++;
		    }
		    stmt.close();
		    rs.close();
		    return pairs;
		}
		catch (SQLException e) {
			fatalError("Cannot load Contig Pairs from database", e);
	    } 
		return null;
	}
	public int writeRepFile(TreeSet <String> ctgMap, String filename) 
	{
		try {
			
			BufferedWriter file = new BufferedWriter(new FileWriter(filename));
			
			Statement stmt = dbConnection.createStatement( 
	        		ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
			
			String strQ = "select contigid,consensus from contig order by contigid";
			
	        ResultSet rs = stmt.executeQuery( strQ );
			
	        int cnt=0;
			while (rs.next())
			{
				String ctgid = rs.getString("contigid");
				String CCS = rs.getString("consensus");
			
				if (ctgMap.contains(ctgid)) 
					file.append(">" + ctgid + "\n" + CCS.replace("*","") + "\n");
			}
			file.close();
			stmt.close();
			return cnt;
		}
		catch (Exception e)
        {
			String s = "Error writing to " + filename;
            ErrorReport.die(e,s);
            return -1;
        }
	}

	public int writeProteinFile(String f) 
	{
		if (isProteinDB) return 0;
		
		try {
			BufferedWriter file = new BufferedWriter(new FileWriter(f));
			
			Statement stmt = dbConnection.createStatement( 
	        		ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
			
			String strQ = "select contigid,consensus, " +
					"o_frame, o_coding_start, o_coding_end, " +
					"p_frame, p_coding_start, p_coding_end " +
					"from contig order by contigid";
			
	        ResultSet rs = stmt.executeQuery( strQ );
			SequenceData seqObj = new SequenceData ("DNA");
			SequenceData aaObj = null;
			
	        int cnt=0;
			while (rs.next())
			{
				String ctgid = rs.getString(1);
				String CCS = rs.getString(2);
				int of = rs.getInt(3);
				int os = rs.getInt(4);
				int oe = rs.getInt(5);
				int pf = rs.getInt(6);
				int ps = rs.getInt(7);
				int pe = rs.getInt(8);
				
				// TODO: just write coding region
				// gaps are '*' in database but SequenceData expects '.' for gaps
				if (pf!=0 || of!=0) {
					seqObj.setSequence( SequenceData.normalizeBases( CCS, '*', '.' ) );
					seqObj.setIsDNA();
					SequenceData nogapObj = seqObj.newSeqDataNoGap();
	
					String msg;		
					if (pf!=0) {aaObj = nogapObj.newSeqDataNTtoAA(pf); 
						msg = " protein ORF frame=" + pf + " start=" + ps + " end=" + pe;}
					else {aaObj = nogapObj.newSeqDataNTtoAA(of); 
						msg = " ORF frame=" + of+ " start=" + os + " end=" + oe;}
					file.append(">" + ctgid + msg + "\n" 
								+ aaObj.getSequence() + "\n");
					cnt++;	
				}
			}
			file.close();
			stmt.close();
			return cnt;
		}
		catch (Exception e)
        {
			String s = "Error writing to " + f;
            ErrorReport.die(e,s);
            return -1;
        }
	}

	//	----------------CONVERTERS for CLOB fields --------------- //
	
	static private String getStringFromReader ( Reader reader ) 
		throws IOException, SQLException 
	{		
		String strReturn = "";
		char[] chChars = new char [ 2000 ]; 
		int nNumRead = 0;
	
		do 
		{
			// Keep filling the buffer until we exhaust the stream
			nNumRead = reader.read( chChars );
			if ( nNumRead <= 0 ) break;		
			// Append to the output string
			strReturn += new String ( chChars, 0, nNumRead );
		} while ( true );
			
		return strReturn;
	}

	static private String createBooleanString ( Boolean b )
	{
		if ( b == null ) return "0";
		else return createBooleanString ( b.booleanValue() );		
	}   
	static private String createBooleanString ( boolean b )
	{
	    if ( b ) return "1";
	    else return "0";
	}    
	
	public Connection getDBconnection () { return dbConnection;}
	public int getAID() { return AID; }
	public boolean isProteinDB() { return isProteinDB;}
	public void setIsProteinDB() {
	    try {
	        if (tableColumnExists("assem_msg", "peptide")) {
	            isProteinDB=true;
	            System.err.println("Protein database");
	        }
	        else {
	            isProteinDB=false;
	        }
	    }
	    catch (Exception e) {ErrorReport.reportError(e, "Checking to see if proteing database");}
	}
	public Connection getConn () {return dbConnection;}
	/****************** instance variables *****************************/
   private Connection dbConnection = null;
   public String strAssemblyID = null;
   public int AID;
   public String PAVE_host = null;
   public String PAVE_db = null;
   public String PAVE_user = null;
   public String PAVE_password = null;
   public String dbFullPath = null;
   private String username, projectpath, assemblydate, annotationdate;
   private boolean isProteinDB = false;
   
   private float fltDbVersion = 0;

}

